//
//  SQLiteHandler.m
//  PeopleLog
//
//  Created by Justin Kim on 11. 6. 16..
//  Copyright 2011 Daum. All rights reserved.
//

#import "SQLHandler.h"


@interface SQLHandler (Private)
-(BOOL)initDataBase;
@end
@implementation SQLHandler

@synthesize db = _db;

-(id)init{
	self = [super init];
    if(self){
        if(![self initDataBase]){
            NSLog(@"database init error");
        }
    }
    return self;
}

+(SQLHandler *)sharedInstance{
    return [[[SQLHandler alloc] init] autorelease];
}
- (void)dealloc {
	[_db close];
    [super dealloc];
}

-(BOOL)initDataBase{
    NSString * DBPath = [NSString stringWithFormat:@"/%@/db.1.0.0.sqlite",[NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES)  objectAtIndex:0] ];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL fileExist = [fileManager fileExistsAtPath:DBPath];
    if(fileExist){
//        NSLog(@"DB file fileExist:%@", DBPath);
    }
    
    self.db = [FMDatabase databaseWithPath:DBPath];
    if (![_db open]) {
//        NSLog(@"Could not open db.");
        return NO;
    }else{
//        NSLog(@"db open");
    }
//    if (YES){
    if ( !fileExist ){
        NSLog(@"create database !!!!!!!!!!!!!!!!!!!!!!!!!");
        [_db executeUpdate:@"drop table item_group"];
        [_db executeUpdate:@"drop table item"];
        [_db executeUpdate:@"drop table shop"];
        [_db executeUpdate:@"drop table cache"];
        [_db executeUpdate:@"drop table bank"];        
        [_db executeUpdate:@"drop table card"];
        [_db executeUpdate:@"drop table write"];        
        [_db executeUpdate:@"drop table tag"];

        [_db executeUpdate:@""  
         "CREATE TABLE item_group"
         "("
         "item_group_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "title VARCHAR NOT NULL,"
         "is_in VARCHAR NOT NULL,"         
         "status VARCHAR NOT NULL,"
         "item_group_order INTEGER"
         ")"
         ];
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:1], @"주수입", @"in", @"s", [NSNumber numberWithInt:0]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:2], @"부수입", @"in", @"s", [NSNumber numberWithInt:1]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:3], @"식비", @"out", @"s", [NSNumber numberWithInt:2]];

        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:4], @"주거", @"out", @"s", [NSNumber numberWithInt:3]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:5], @"생활", @"out", @"s", [NSNumber numberWithInt:4]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:6], @"의류", @"out", @"s", [NSNumber numberWithInt:5]];

        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:7], @"육아", @"out", @"s", [NSNumber numberWithInt:6]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:8], @"미용", @"out", @"s", [NSNumber numberWithInt:7]];

        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:9], @"교육", @"out", @"s", [NSNumber numberWithInt:8]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:10], @"의료", @"out", @"s", [NSNumber numberWithInt:9]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:11], @"문화생활", @"out", @"s", [NSNumber numberWithInt:10]];
            
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:12], @"교통", @"out", @"s", [NSNumber numberWithInt:11]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:13], @"통신", @"out", @"s", [NSNumber numberWithInt:12]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:14], @"세금", @"out", @"s", [NSNumber numberWithInt:13]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:15], @"금융", @"out", @"s", [NSNumber numberWithInt:14]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:16], @"보험", @"out", @"s", [NSNumber numberWithInt:15]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:17], @"경조사", @"out", @"s", [NSNumber numberWithInt:16]];
        
        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:18], @"차량", @"out", @"s", [NSNumber numberWithInt:17]];

        [_db executeUpdate:@"insert into item_group (item_group_id, title, is_in, status, item_group_order) values (?, ?, ?, ?, ?)"
         , [NSNumber numberWithInt:19], @"기타", @"out", @"s", [NSNumber numberWithInt:18]];

        
        
        
        
        
        
        
        

        [_db executeUpdate:@""  
         "CREATE TABLE item"
         "("
         "item_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "item_group_id INTEGER NOT NULL,"
         "title VARCHAR NOT NULL,"
         "status VARCHAR NOT NULL,"
         "item_order INTEGER"
         ")"
         ];
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:1], @"급여", @"s", [NSNumber numberWithInt:0]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"보너스", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"이자", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"임대료", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"기타금융", @"s", [NSNumber numberWithInt:3]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:3], @"주식", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:3], @"부식", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:3], @"간식", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:3], @"외식", @"s", [NSNumber numberWithInt:3]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:4], @"임대료", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:4], @"관리비", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:4], @"수리비", @"s", [NSNumber numberWithInt:2]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:5], @"가전", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:5], @"가구", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:5], @"주방", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:5], @"침구", @"s", [NSNumber numberWithInt:3]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:6], @"의류", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:6], @"잡화", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:6], @"세탁", @"s", [NSNumber numberWithInt:2]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:7], @"의류", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:7], @"잡화", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:7], @"기저귀", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:7], @"이유식", @"s", [NSNumber numberWithInt:3]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:7], @"분유", @"s", [NSNumber numberWithInt:4]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:7], @"장난감", @"s", [NSNumber numberWithInt:5]];        
        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:8], @"화장품", @"s",[NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:8], @"미용실", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:8], @"목욕", @"s", [NSNumber numberWithInt:2]];        
        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:9], @"학비", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:9], @"교재", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:9], @"학용품", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:9], @"학원", @"s", [NSNumber numberWithInt:3]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:9], @"과외", @"s", [NSNumber numberWithInt:4]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:10], @"병원", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:10], @"약품", @"s", [NSNumber numberWithInt:1]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:11], @"영화", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:11], @"공연", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:11], @"놀이공원", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:11], @"레져", @"s", [NSNumber numberWithInt:3]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:11], @"음반/책", @"s", [NSNumber numberWithInt:4]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:12], @"지하철", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:12], @"버스", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:12], @"택시", @"s",[NSNumber numberWithInt: 2]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:13], @"휴대폰", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:13], @"전화", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:13], @"인터넷", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:13], @"학용품", @"s", [NSNumber numberWithInt:3]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:14], @"학용품", @"s", [NSNumber numberWithInt:0]];        
        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:15], @"대출이자", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:15], @"대출금", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:15], @"CMA", @"s", [NSNumber numberWithInt:2]];        

        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:16], @"생명", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:16], @"화재", @"s", [NSNumber numberWithInt:1]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:17], @"결혼", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:17], @"졸업", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:17], @"생일", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:17], @"조의", @"s", [NSNumber numberWithInt:3]];        
        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:18], @"주유", @"s", [NSNumber numberWithInt:0]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:18], @"세차", @"s", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:18], @"수리", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:18], @"보험", @"s", [NSNumber numberWithInt:3]];        

        [_db executeUpdate:@""  
         "CREATE TABLE shop"
         "("
         "shop_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "title VARCHAR NOT NULL,"
         "status VARCHAR NOT NULL,"
         "shop_order INTEGER"
         ")"
         ];
        [_db executeUpdate:@"insert into shop (shop_id, title, status, shop_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:0], @"", @"d", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into shop (shop_id, title, status, shop_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:1], @"롯데마트", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into shop (shop_id, title, status, shop_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"이마트", @"s", [NSNumber numberWithInt:3]];        

        
        [_db executeUpdate:@""  
         "CREATE TABLE cash"
         "("
         "cash_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "title VARCHAR NOT NULL,"
         "is_default VARCHAR NOT NULL,"
         "status VARCHAR NOT NULL,"         
         "cash_order INTEGER"
         ")"
         ];
        [_db executeUpdate:@"insert into cash (cash_id, title, is_default, status, cash_order) values (?, ?, ?, ?, ?)",
         [NSNumber numberWithInt:0], @"", @"n", @"d", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into cash (cash_id, title, is_default, status, cash_order) values (?, ?, ?, ?, ?)",
         [NSNumber numberWithInt:1], @"내지갑", @"y", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into cash (cash_id, title, is_default, status, cash_order) values (?, ?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"아빠지갑", @"n", @"s", [NSNumber numberWithInt:3]];        

        
        [_db executeUpdate:@""  
         "CREATE TABLE bank"
         "("
         "bank_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "title VARCHAR NOT NULL,"
         "type VARCHAR DEFAULT 0,"
         "status VARCHAR NOT NULL,"         
         "bank_order INTEGER"
         ")"
         ];
        [_db executeUpdate:@"insert into bank (bank_id, title, type, status, bank_order) values (?, ?, ?, ?, ?)",
         [NSNumber numberWithInt:1], @"", @"d", [NSNumber numberWithInt:1]];

        
        [_db executeUpdate:@""  
         "CREATE TABLE card"
         "("
         "card_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "title VARCHAR NOT NULL,"
         "bank_id INTEGER ,"
         "start_day INTEGER ,"
         "end_day INTEGER ,"
         "pay_day INTEGER ,"
         "is_check VARCHAR NOT NULL,"
         "status VARCHAR NOT NULL,"
         "card_order INTEGER"
         ")"
         ];
        [_db executeUpdate:@"insert card (card_id, title, status, card_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:0], @"", @"d", [NSNumber numberWithInt:1]];        
        
        [_db executeUpdate:@""  
         "CREATE TABLE write"
         "("
         "write_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "item_group_id INTEGER NOT NULL,"
         "item_id INTEGER  NOT NULL,"
         "title VARCHAR NOT NULL,"
         "account INTEGER NOT NULL,"
         "shop_id INTEGER ,"
         "cash_id INTEGER ,"
         "bank_id INTEGER ,"
         "card_id INTEGER ,"
         "write_day INTEGER ,"
         "repeat_write_id INTEGER ,"
         "status VARCHAR NOT NULL"
         ")"
         ];

        
        [_db executeUpdate:@""  
         "CREATE TABLE tag"
         "("
         "tag_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
         "title VARCHAR NOT NULL,"
         "status VARCHAR NOT NULL,"
         "tag_order INTEGER"
         ")"
         ];            
        [_db executeUpdate:@"insert into tag (tag_id, title, status, tag_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:0], @"", @"d", [NSNumber numberWithInt:1]];        
        [_db executeUpdate:@"insert into tag (tag_id, title, status, tag_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:1], @"과소비", @"s", [NSNumber numberWithInt:2]];        
        [_db executeUpdate:@"insert into tag (tag_id, title, status, tag_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:2], @"알뜰소비", @"s", [NSNumber numberWithInt:3]];        
        [_db executeUpdate:@"insert into tag (tag_id, title, status, tag_order) values (?, ?, ?, ?)",
         [NSNumber numberWithInt:3], @"돌발지출", @"s", [NSNumber numberWithInt:4]];        
        
        [_db executeUpdate:@""  
         "CREATE TABLE tag_mapping"
         "("
         "tag_id INTEGER DEFAULT 0,"
         "write_id INTEGER DEFAULT 0,"
		 "PRIMARY KEY (tag_id, write_id)"
         ")"
         ];
        

        
        NSLog(@"init tag");
    }
    return YES;
}

- (NSNumber*)nextOrderWithTableName:(NSString*)tableName{
    int nextOrder = 0;
    NSString* qeury = [NSString stringWithFormat:@"select max(%@_order)+1 from %@", tableName, tableName];
    
    FMResultSet *rs = [_db executeQuery:qeury];
	if ([rs next]) {
		nextOrder = [rs intForColumnIndex:0];
	}
	[rs close];    
    return [NSNumber numberWithInt:nextOrder];
}
#pragma mark -
#pragma mark  Common
- (void)updateOrder:(NSString*)tableName pkId:(NSNumber*)pkId order:(NSNumber*)order{
    NSString* sql = [NSString stringWithFormat:@"update %@ set %@_order = ? where %@_id = ?", tableName, tableName, tableName];
//    NSLog(@"updateOrder:%@", sql);
    [_db executeUpdate:sql, order, pkId];
}
#pragma mark -
#pragma mark  ItemGroup
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createItemGroupWithTitle:(NSString*)title 
                           isIn:(NSString*)isIn{
    [_db executeUpdate:@"insert into item_group (title, is_in, status, item_group_order) values ( ?, ?, ?, ?)"
     , title, isIn, @"s", [self nextOrderWithTableName:@"item_group"]];
    
    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(item_group_id) from item_group"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
   	[rs close];
    return lastId;
}
- (NSDictionary*)findItemGroupById:(NSNumber*)itemGroupId{
    FMResultSet *rs =[_db executeQuery:@"select item_group_id, title, is_in, status, item_group_order from item_group where item_group_id = ?", itemGroupId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
}
- (NSArray*)findAllItemGroupByIsIn:(NSString*)isIn{
	FMResultSet *rs = [_db executeQuery:@"select item_group_id, title, is_in, status, item_group_order from item_group where is_in=? and status = ? order by item_group_order", isIn, @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;    
}
- (NSArray*)findAllItemGroup{
    FMResultSet *rs = [_db executeQuery:@"select item_group_id, title, is_in, status, item_group_order from item_group where status = ? order by item_group_order", @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;    

}
- (void)updateItemGroupId:(NSNumber*)itemGroupId
                    title:(NSString*)title 
                     isin:(NSString*)isIn{
//    NSLog(@"updateItemGroupTitleIsin: %@", dic);
    if(![_db executeUpdate:@"update item_group set title = ?, is_in = ? where item_group_id = ?", title, isIn, itemGroupId]){
        NSLog(@"updateItemGroupTitleIsin error");
    }
}
- (void)deleteItemGroupById:(NSNumber*)itemGroupId{
    if(![_db executeUpdate:@"update item_group set status = ? where item_group_id = ?", @"d", itemGroupId]){
        NSLog(@"deleteItemGroup error");
    }
}
#pragma mark -
#pragma mark  Item
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createItemWithItemGroupId:(NSNumber*)itemGroupId
                           title:(NSString*)title{
    [_db executeUpdate:@"insert into item (item_group_id, title, status, item_order) values (?, ?, ?, ?)",
     itemGroupId, title, @"s", [self nextOrderWithTableName:@"item"]];        
    
    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(item_id) from item"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    
}
- (NSDictionary*)findItemById:(NSNumber*)itemId{
    FMResultSet *rs =[_db executeQuery:@"select item_id, item_group_id, title, status, item_order from item where item_id = ?", itemId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
    
}
- (NSArray*)findAllItemByItemGroupById:(NSNumber*)itemGroupId{
	FMResultSet *rs = [_db executeQuery:@"select item_id, item_group_id, title, status, item_order from item where item_group_id = ? and status = ? order by item_order", itemGroupId, @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        
}
- (void)updateItemId:(NSNumber*)itemId
               title:(NSString*)title{
    if(![_db executeUpdate:@"update item set title = ? where item_id = ?", title, itemId]){
        NSLog(@"updateItemTitle error");
    }
}
- (void)deleteItemById:(NSNumber*)itemId{
    if(![_db executeUpdate:@"update item set status = ? where item_id = ?", @"d", itemId]){
        NSLog(@"updateItemTitle error");
    }    
}
#pragma mark -
#pragma mark  Shop
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createShopWithTitle:(NSString*)title{
    [_db executeUpdate:@"insert into shop (title, status, shop_order) values (?, ?, ?)",
     title, @"s", [self nextOrderWithTableName:@"shop"]];        

    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(shop_id) from shop"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    

}
- (NSDictionary*)findShopById:(NSNumber*)shopId{
    FMResultSet *rs =[_db executeQuery:@"select * from shop where shop_id = ?", shopId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
    
}
- (NSArray*)findAllShop{
    FMResultSet *rs = [_db executeQuery:@"select * from shop where status = ? order by shop_order", @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        

}
- (void)updateShopId:(NSNumber*)shopId
               title:(NSString*)title{
    if(![_db executeUpdate:@"update shop set title = ? where shop_id = ?", title, shopId]){
        NSLog(@"updateShopTitle error");
    }    
}
- (void)deleteShopById:(NSNumber*)shopId{
    if(![_db executeUpdate:@"update shop set status = ? where shop_id = ?", @"d", shopId]){
        NSLog(@"updateShopTitle error");
    }    
}

#pragma mark -
#pragma mark  Cash
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createCashWithTitle:(NSString*)title 
                 isDefault:(BOOL)isDefault{
    [_db executeUpdate:@"insert into cash (title, is_default, status, cash_order) values (?, ?, ?, ?)",
     title, isDefault?@"y":@"n", @"s", [self nextOrderWithTableName:@"cash"]];        

    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(cash_id) from cash"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    
    
}
- (NSDictionary*)findDefaultCash{
    FMResultSet *rs =[_db executeQuery:@"select * from cash where is_default = 'y'"];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;    
}
- (NSDictionary*)findCashById:(NSNumber*)cashId{
    FMResultSet *rs =[_db executeQuery:@"select * from cash where cash_id = ?", cashId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
    
}
- (NSArray*)findAllCash{
    FMResultSet *rs = [_db executeQuery:@"select * from cash where status = ? order by cash_order", @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        
    
}
- (void)updateCashId:(NSNumber*)cashId
               title:(NSString*)title
           isDefault:(BOOL)isDefault{
    if(![_db executeUpdate:@"update cash set title = ?, is_default = ? where cash_id = ?", title, isDefault?@"y":@"n", cashId]){
        NSLog(@"updateCashTitle error");
    }    
}
- (void)deleteCashById:(NSNumber*)cashId{
    if(![_db executeUpdate:@"update cash set status = ? where cash_id = ?", @"d", cashId]){
        NSLog(@"updateCashTitle error");
    }    
}

#pragma mark -
#pragma mark  Card
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createCardIdWithTitle:(NSString*)title 
                      bankId:(NSNumber*)bankId
                    startDay:(NSNumber*)startDay
                      endDay:(NSNumber*)endDay
                      payDay:(NSNumber*)payDay
                     isCheck:(BOOL)isCheck{
    NSLog(@"createCardIdWithTitle : %@, %d, %d, %d, %d, %d", title, [bankId intValue], [startDay intValue], [endDay intValue], [payDay intValue], isCheck);

    [_db executeUpdate:@"insert into card (title, bank_id, start_day, end_day, pay_day, is_check, status, card_order) values (?, ?, ?, ?, ?, ?, ?, ?)",
     title, bankId, startDay, endDay, payDay, isCheck?@"y":@"n", @"s", [self nextOrderWithTableName:@"card"]];        
    
    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(card_id) from card"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    
        
}
- (NSDictionary*)findCardById:(NSNumber*)cardId{
    FMResultSet *rs =[_db executeQuery:@"select * from card where card_id = ?", cardId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;

}
- (NSArray*)findAllCard{
    FMResultSet *rs = [_db executeQuery:@"select * from card where status = ? order by card_order", @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        

}
- (void)updateCardId:(NSNumber*)cardId
               title:(NSString*)title
              bankId:(NSNumber*)bankId
            startDay:(NSNumber*)startDay
              endDay:(NSNumber*)endDay
              payDay:(NSNumber*)payDay
             isCheck:(BOOL)isCheck{
    if(![_db executeUpdate:@"update card set title = ?, bank_id = ?, start_day = ?, end_day = ?, pay_day = ?, is_check = ? where card_id = ?",
         title, bankId, startDay, endDay, payDay, isCheck?@"y":@"n", cardId]){
        NSLog(@"updateCardTitle error: %d, %@, %d, %d, %d, %d, %d", [cardId intValue], title, [bankId intValue], [startDay intValue], [endDay intValue], [payDay intValue], isCheck);
    }    
}
- (void)deleteCardById:(NSNumber*)cardId{
    if(![_db executeUpdate:@"update card set status = ? where card_id = ?", @"d", cardId]){
        NSLog(@"updateCardTitle error");
    }    
}


#pragma mark -
#pragma mark  Bank
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createBankWithTitle:(NSString*)title 
                      type:(NSNumber*)type{
    NSLog(@"create bank:%@ ,%d", title, type);
    [_db executeUpdate:@"insert into bank (title, type, status, bank_order) values (?, ?, ?, ?)",
     title, type, @"s", [self nextOrderWithTableName:@"bank"]];        
    
    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(bank_id) from bank"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    
    
}
- (NSDictionary*)findBankById:(NSNumber*)bankId{
    FMResultSet *rs =[_db executeQuery:@"select * from bank where bank_id = ?", bankId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
    
}
- (NSArray*)findAllBank{
    FMResultSet *rs = [_db executeQuery:@"select * from bank where status = ? order by bank_order", @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        
    
}
- (void)updateBankId:(NSNumber*)bankId
               title:(NSString*)title
                type:(NSNumber*)type{
//    NSLog(@"saveActoin: %d, %@, %d", [bankId intValue], title , [type intValue]);
    if(![_db executeUpdate:@"update bank set title = ?, type = ? where bank_id = ?", title, type, bankId]){
        NSLog(@"updateBankTitle error");
    }    
}
- (void)deleteBankById:(NSNumber*)bankId{
    if(![_db executeUpdate:@"update bank set status = ? where bank_id = ?", @"d", bankId]){
        NSLog(@"updateCardTitle error");
    }    
}

#pragma mark -
#pragma mark  Tag
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createTagWithTitle:(NSString*)title{
    [_db executeUpdate:@"insert into tag (title, status, tag_order) values (?, ?, ?)",
     title, @"s", [self nextOrderWithTableName:@"tag"]];        
    
    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(tag_id) from tag"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    
    
}
- (NSDictionary*)findTagById:(NSNumber*)tagId{
    FMResultSet *rs =[_db executeQuery:@"select * from tag where tag_id = ?", tagId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
    
}
- (NSArray*)findAllTag{
    FMResultSet *rs = [_db executeQuery:@"select * from tag where status = ? order by tag_order", @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        
    
}
- (NSArray*)findallByWriteId:(NSNumber*)writeId{
    FMResultSet *rs = [_db executeQuery:@"select a.tag_id, a.title from tag a, tag_mapping b where a.tag_id = b.tag_id and b.write_id = ? and status = ? order by tag_order", writeId, @"s"];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        
}
- (void)createTagMappingWithTagId:(NSNumber*)tagId
                          writeId:(NSNumber*)writeId{
    [_db executeUpdate:@"insert into  tag_mapping (tag_id, write_id) values (?, ?)",
     tagId, writeId];    
}
- (void)updateTagId:(NSNumber*)tagId
              title:(NSString*)title{
    if(![_db executeUpdate:@"update tag set title = ? where tag_id = ?", title, tagId]){
        NSLog(@"updateBankTitle error");
    }    

}
- (void)deleteTagById:(NSNumber*)tagId{
    if(![_db executeUpdate:@"update tag set status = ? where tag_id = ?", @"d", tagId]){
        NSLog(@"updateCardTitle error");
    }    
}


#pragma mark -
#pragma mark  Write
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
- (int)createWriteWithItemGruopId:(NSNumber*)itemGroupId
                           itemId:(NSNumber*)itemId
                            title:(NSString*)title 
                          account:(NSNumber*)account
                           shopId:(NSNumber*)shopId
                           cashId:(NSNumber*)cashId
                           bankId:(NSNumber*)bankId
                           cardId:(NSNumber*)cardId
                         writeDay:(NSNumber*)writeDay
                    repeatWriteid:(NSNumber*)repeatWirteId
                           status:(NSString*)status{
    [_db executeUpdate:@"insert into write (item_group_id, item_id, title, account, cash_id, shop_id, bank_id, card_id, write_day, repeat_wirte_id, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
     itemGroupId, title, account, shopId, cashId, bankId, cardId, writeDay, repeatWirteId,  @"s"];
    
    int lastId = 0;
    FMResultSet *rs = [_db executeQuery:@"select max(write_id) from write"];
	if ([rs next]) {
		lastId = [rs intForColumnIndex:0];
	}
    [rs close];
    return lastId;    
    
}
- (NSDictionary*)findWriteById:(NSNumber*)writeId{
    FMResultSet *rs =[_db executeQuery:@"select * from write where write_id = ?", writeId];
    NSDictionary *dic = nil;
    if ([rs next]) {
        dic = [rs resultDict];
    }
   	[rs close];
    return dic;
    
}
- (NSArray*)findAllWriteByStartDay:(NSNumber*)startDay endDay:(NSNumber*)endDay{
    FMResultSet *rs = [_db executeQuery:@"select * from write where status = ? and wirteDay between ? and ? order by tag_order", @"s", startDay, endDay];
	NSMutableArray* array = [NSMutableArray array];
	while ([rs next]) {
		[array addObject:[rs resultDict]];
	}
	[rs close];	
	return array;        
    
    
}

@end
